/*Data Access Code*/

var scrumdb;
// scrumdb=window.openDatabase("ScrumDB", "", "My Scrum database", 1024);

//initially when nothing is selected
var current_project = -1;
var current_pbi = -1;
var current_sbi = -1;

var pbis;
var sbis = new Array();

function initDatabase() {
	try {
		//checking whether browser is suporting it
		if(window.openDatabase) {
			var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
			if(db.version != "1") {
				db.changeVersion(db.version, "1", function(tx) {
					// User's first visit.  Initialize database.
					tx.executeSql("CREATE TABLE IF NOT EXISTS statustb(status_id INTEGER PRIMARY KEY,status_name NVARCHAR(20) NOT NULL, status_desc NVARCHAR(100));");
					tx.executeSql("CREATE TABLE IF NOT EXISTS prioritytb(priority_id INTEGER PRIMARY KEY,priority_name NVARCHAR(20) NOT NULL UNIQUE, priority_desc NVARCHAR(100));");
					tx.executeSql("CREATE TABLE IF NOT EXISTS projecttb(project_id INTEGER PRIMARY KEY NOT NULL,project_name NVARCHAR(20) NOT NULL UNIQUE, project_desc NVARCHAR(100),project_status NUMBER NOT NULL, project_startdate TEXT NOT NULL, project_enddate TEXT NOT NULL,no_of_res INTEGER NOT NULL,created_date NVARCHAR(100) NOT NULL,modified_date NVARCHAR(100) NOT NULL,FOREIGN KEY (project_status) REFERENCES statustb(status_id));");

					tx.executeSql("CREATE TABLE IF NOT EXISTS pbitb(pbi_autoid INTEGER PRIMARY KEY AUTOINCREMENT,pbi_id INTEGER NOT NULL,project_id NUMBER NOT NULL,priority_id NUMBER NOT NULL,pbi_dependency NUMBER,sprintid NUMBER,estimated_hours NUMBER NOT NULL,buissness_process NVARCHAR(200),grouping NVARCHAR(200),pbi_userstory NVARCHAR(200),accp_cri NVARCHAR(200),status NUMBER NOT NULL,initiated_by NVARCHAR(50),res NUMBER NOT NULL,start TEXT NOT NULL,stop TEXT NOT NULL,FOREIGN KEY (status) REFERENCES statustb(status_id));");

					tx.executeSql("CREATE TABLE IF NOT EXISTS sbitb(sbi_autoid INTEGER PRIMARY KEY AUTOINCREMENT,pbi_id INTEGER NOT NULL,sbi_name TEXT NOT NULL,status NUMBER NOT NULL,estimated_hours NUMBER NOT NULL,completed_hours NUMBER NOT NULL,FOREIGN KEY (status) REFERENCES statustb(status_id),FOREIGN KEY (pbi_id) REFERENCES pbitb(pbi_autoid));");

					/*

					 */

				}, null, function() {
					load_initialdata();
				});
			} else {
				console.log('database already initialized');

			}
		} else {
			console.log('Databases are not supported in this browser.');
		}
	} catch (e) {

		if(e == 2) {
			// Version number mismatch.
			console.log("Invalid database version.");
		} else {
			console.log("Unknown error " + e + ".");
		}
		return;
	}
}

function load_initialdata() {

	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(transaction) {

			//populating statustable
			transaction.executeSql("insert into statustb VALUES ('1', 'Not Started','project not yet started');");
			transaction.executeSql("insert into statustb VALUES ('2', 'In Progress','project not yet started');");
			transaction.executeSql("insert into statustb VALUES ('3', 'Hold','project not yet started');");
			transaction.executeSql("insert into statustb VALUES ('4', 'Completed','project not yet started');");
			transaction.executeSql("insert into statustb VALUES ('5', 'In Progress','SBI is in progress');");
			transaction.executeSql("insert into statustb VALUES ('6', 'Review','SBI is review');");
			transaction.executeSql("insert into statustb VALUES ('7', 'Blocked','SBI is blocked');");
			transaction.executeSql("insert into statustb VALUES ('8', 'Done','SBI is done');");

			//populating priority table
			transaction.executeSql("insert into prioritytb VALUES ('1', 'High','project priority is high');");
			transaction.executeSql("insert into prioritytb VALUES ('2', 'Above Normal','project priority is above normal');");
			transaction.executeSql("insert into prioritytb VALUES ('3', 'Normal','project priority is normal');");
			transaction.executeSql("insert into prioritytb VALUES ('4', 'Below Normal','project priority is below normal');");
			transaction.executeSql("insert into prioritytb VALUES ('5', 'Lowest','project priority is lowest');");

			//transaction.executeSql("insert into projecttb VALUES ('9001', 'GOM Petroweb','UP Stream GOM Petroweb',1,'10/10/2011','1/4/2012','1',"+ new Date().getTime()+","+new Date().getTime()+ ");");
			addnewproject('9001', 'Demo Project Data', 'Demo Description', '1', '10/10/2011', '1/4/2012', '1');
			

			var query = "insert into pbitb(pbi_id,project_id,priority_id,pbi_dependency,sprintid,estimated_hours,buissness_process,grouping,pbi_userstory,accp_cri,status,initiated_by,res,start,stop) VALUES ('1','9001','1',null,null,'10','buissness process','grouping','user story','Acceptance Criteria','1','Yogesh','10','10/10/2011','1/3/2012')";
			transaction.executeSql(query);
			query = "insert into pbitb(pbi_id,project_id,priority_id,pbi_dependency,sprintid,estimated_hours,buissness_process,grouping,pbi_userstory,accp_cri,status,initiated_by,res,start,stop) VALUES ('2','9001','1',null,null,'5','buissness process1','grouping1','user story1','Acceptance Criteria1','1','Yogesh1','10','10/10/2011','1/3/2012')";
			transaction.executeSql(query);

			addnewpbi('3', '9001', '1', '1', '12', 'buissness process1', 'grouping1', 'user story1', 'Acceptance Criteria1', '1', 'Yogesh', '12', '8/10/2011', '1/5/2012');
			/*
			query = "insert into pbitb(pbi_id,project_id,priority_id,pbi_dependency,sprintid,estimated_hours,buissness_process,grouping,pbi_userstory,accp_cri,status,initiated_by,res,start,stop) VALUES ('3','9001','1','1',null,'12','buissness process1','grouping1','user story1','Acceptance Criteria1','1','Yogesh','12','8/10/2011','1/5/2012')";
			transaction.executeSql(query);
			*/

			//query = "insert into sbitb(pbi_id,sbi_name,status,estimated_hours,completed_hours) VALUES ('1','Design','5','10','0')";
			//transaction.executeSql(query);
			addnewsbi('1', 'Design', '5', '10', '0');
			addnewsbi('1', 'Coding', '5', '10', '0');
			addnewsbi('2', 'Design', '6', '15', '0');
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function resetdatabase() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(transaction) {

			transaction.executeSql("DROP TABLE IF EXISTS sbitb;");

			transaction.executeSql("DROP TABLE IF EXISTS pbitb;");

			//must me deleted before status
			transaction.executeSql("DROP TABLE IF EXISTS projecttb;");

			transaction.executeSql("DROP TABLE IF EXISTS statustb;");
			transaction.executeSql("DROP TABLE IF EXISTS prioritytb;");

		});
		db.changeVersion(db.version, "0", function() {
			console.log('database is reseted');
		});
	}
}

/*Adding New Item*/

/*Add New Project*/
function addnewproject(proj_id, proj_name, proj_desc, status, start_date, end_date, no_res) {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(transaction) {
			//Adding new project
			var query = "insert into projecttb VALUES (".concat("'", proj_id, "'", ",", "'", proj_name, "'", ",", "'", proj_desc, "'", ",", "'", status, "'", ",", "'", start_date, "'", ",", "'", end_date, "'", ",", "'", no_res, "'", ",", new Date().getTime(), ",", new Date().getTime(), ");");
			transaction.executeSql(query);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function addnewprojUI() {
	var proj_id = $('#addproj_projid').val();
	var proj_name = $('#addproj_name').val();
	var proj_desc = $('#addproj_desc').val();
	var proj_status = $('#addproj_status :selected').text();
	switch (proj_status) {
		case 'In Progress':
			proj_status = 1;
			break;
		case 'Not Started':
			proj_status = 2;
			break;
		case 'Hold':
			proj_status = 3;
			break;
		case 'Completed':
			proj_status = 4;
			break;
	}
	$('#addproj_status').selectmenu('refresh');
	var proj_start = $('#addproj_startdate').val();
	var proj_stop = $('#addproj_enddate').val();
	var proj_res = $('#addproj_res').val();
	addnewproject(proj_id, proj_name, proj_desc, proj_status, proj_start, proj_stop, proj_res);

}

/*var query = "insert into pbitb(pbi_id,project_id,priority_id,pbi_dependency,sprintid,estimated_hours,buissness_process,grouping,pbi_userstory,accp_cri,status,initiated_by,res,start,stop) VALUES ('1','9001','1',null,null,'10','buissness process','grouping','user story','Acceptance Criteria','1','Yogesh','10','10/10/2011','1/3/2012')";*/

/*PBIs*/
function addnewpbi(pbi_id, project_id, priority_id, pbi_dependency, estimated_hours, buissness_process, grouping, pbi_userstory, accp_cri, status, initiated_by, res, pbi_start, pbi_stop) {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(tr) {
			//Adding new project
			var query = "insert into pbitb(pbi_id,project_id,priority_id,pbi_dependency,status,estimated_hours,buissness_process,grouping,pbi_userstory,accp_cri,status,initiated_by,res,start,stop) VALUES (".concat("'", pbi_id, "'", ",", "'", project_id, "'", ",", "'", priority_id, "'", ",", "'", pbi_dependency, "'", ",", null, ",", "'", estimated_hours, "'", ",", "'", buissness_process, "'", ",", "'", grouping, "'", ",", "'", pbi_userstory, "'", ",", "'", accp_cri, "'", ",", "'", status, "'", ",", "'", initiated_by, "'", ",", "'", res, "'", ",", "'", pbi_start, "'", ",", "'", pbi_stop, "'", ",", ");");
			tr.executeSql(query);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function addnewpbiUI()
{
	var pbi_id=$('#addpbi_id').val();
	var pbi_priid = $('#addpbi_proid :selected').text();
	switch (pbi_priid) {
		case 'High':
			pbi_priid = 1;
			break;
		case 'Above Normal':
			pbi_priid = 2;
			break;
		case 'Normal':
			pbi_priid = 3;
			break;
		case 'Below Normal':
			pbi_priid = 4;
			break;
		case 'Lowest':
			pbi_priid = 5;
			break;
	}
	
	var pbi_dep=$('#addpbi_dependency').val();
	var pbi_bus=$('#add_pbi_buiss').val();
	var pbi_gro=$('#addpbi_grouping').val();
	var pbi_user=$('#addpbi_userstory').val();
	var pbi_accp=$('#addpbi_accpcri').val();
	
	
	var pbi_status = $('#addpbi_status :selected').text();
	switch (pbi_status) {
		case 'In Progress':
			pbi_status = 1;
			break;
		case 'Not Started':
			pbi_status = 2;
			break;
		case 'Hold':
			pbi_status = 3;
			break;
		case 'Completed':
			pbi_status = 4;
			break;
	}
	var pbi_res=$('#addpbi_res').val();
	var pbi_by=$('#addpbi_init').val();
	var pbi_start=$('#addpbi_startdate').val();
	var pbi_end=$('#addpbi_end').val();
	
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(transaction) {
			//Adding new project
			query = "insert into pbitb(pbi_id,project_id,priority_id,pbi_dependency,sprintid,estimated_hours,buissness_process,grouping,pbi_userstory,accp_cri,status,initiated_by,res,start,stop) VALUES ('".concat(pbi_id,"','", current_project ,"','", pbi_priid ,"',null,null,'",pbi_priid,"','",pbi_bus,"','",pbi_gro,"','",pbi_user,"','",pbi_accp,"','",pbi_status,"','",pbi_by,"','",pbi_res,"','",pbi_start,"','",pbi_start,"')");
			transaction.executeSql(query);
		});
	} else {
		console.log('initialize the databasefirst');
	}

}


/*SBI*/
//insert into sbitb(pbi_id,sbi_name,status,estimated_hours,completed_hours) VALUES ('1','Design','5','10','0')"

function addnewsbi(pbi_id, sbi_name, status, estimated_hours, completed_hours) {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(transaction) {
			//Adding new project
			var query = "insert into sbitb(pbi_id,sbi_name,status,estimated_hours,completed_hours) VALUES (".concat("'", pbi_id, "'", ",", "'", sbi_name, "'", ",", "'", status, "'", ",", "'", estimated_hours, "'", ",", "'", completed_hours, "'", ");");
			transaction.executeSql(query);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function addnewsbiui() {
	var sbi_name_1 = $('#addsbi_name').val();
	var status_1 = $('#addsbi_status :selected').text();
	var estimated_hours_1 = $('#addsbi_esthours').val();
	var completed_hours_1 = $('#addsbi_completedhrs').val()

	addnewsbi(current_pbi, sbi_name_1, status_1, estimated_hours_1, completed_hours_1);
	/*
	 $('#addsbi_name').val("");
	 $('#addsbi_esthours').val("");
	 $('#addsbi_completedhrs').val("0");
	 $('#addsbi_completedhrs').slider('refresh');
	 */
}

function loaddata() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(transaction) {
			//Adding new project
			//transaction.executeSql("insert into projecttb VALUES ('9001', 'GOM Petroweb','UP Stream GOM Petroweb',1,'10/10/2011','1/4/2012','1'," + new Date().getTime() + "," + new Date().getTime() + ");");
			var query = "select * from projecttb;";
			transaction.executeSql(query);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function errorHandler(transaction, error) {
	console.log('Oops.  Error was ' + error.message + ' (Code ' + error.code + ')');
}

function nullDataHandler(transaction, results) {
	// Handle the results
	var string = "Data handler Error:\n";

	for(var i = 0; i < results.rows.length; i++) {
		var row = results.rows.item(i);
		string = string + row['name'] + " (ID " + row['id'] + ")\n";
	}
	console.log(string);
}

/*Projects page*/

function get_projects_list() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(tx) {
			tx.executeSql('SELECT * FROM projecttb', [], function(tx, results) {
				var projlis = "<li data-role='list-divider'>Projects</li>";
				for(var i = 0; i < results.rows.length; i++) {
					var projtaglist = "";
					var project_id = results.rows.item(i)['project_id'];
					var project_name = results.rows.item(i)['project_name'];
					var project_desc = results.rows.item(i)['project_desc'];
					projlis = projlis + "<li onclick='current_project=".concat(project_id, ";'><a href='#projdetialspg'><img src='images/Folder-Blue-Doc-icon.png'/><h3>", project_id, " - ", project_name, "</h3><p>", project_desc, "</p></a><a href='#'>Edit Details</a></li>");
				}
				$('#projectslv').empty();
				$('#projectslv').append(projlis);
				$('#projectslv').listview("refresh");
			}, null);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

/*Get PBI List*/
function get_pbi_list() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(tx) {
			var query = "SELECT * FROM pbitb Where project_id=".concat("'", current_project, "'");
			tx.executeSql(query, [], function(tx, results) {
				var pbilis = "<li data-role='list-divider'>PBIs</li>";
				for(var i = 0; i < results.rows.length; i++) {
					var projtaglist = "";
					var pbi_autoid = results.rows.item(i)['pbi_autoid'];
					var pbi_id = results.rows.item(i)['pbi_id'];
					var pbi_userstory = results.rows.item(i)['pbi_userstory'];
					pbilis = pbilis + "<li onclick='current_pbi=".concat(pbi_autoid, ";'><a href='#pbidetialspg'><img src='images/Folder-Blue-System-icon.png'/><h3>", pbi_id, "</h3><p>", pbi_userstory, "</p></a><a href='#'>Edit Details</a></li>");
				}
				$('#pbilv').empty();
				$('#pbilv').append(pbilis);
				$('#pbilv').listview("refresh");
			}, null);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

/*Get SBI List*/
function get_sbi_list() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(tx) {
			var query = "SELECT * FROM sbitb Where pbi_id=".concat("'", current_pbi, "'");
			tx.executeSql(query, [], function(tx, results) {
				var sbilis = "<li data-role='list-divider'>SBIs</li>";
				for(var i = 0; i < results.rows.length; i++) {
					var projtaglist = "";
					var sbi_autoid = results.rows.item(i)['sbi_autoid'];
					var pbi_autoid = results.rows.item(i)['pbi_id'];
					var sbi_name = results.rows.item(i)['sbi_name'];
					var status = results.rows.item(i)['status'];
					var pbi_userstory = results.rows.item(i)['pbi_userstory'];
					sbilis = sbilis + "<li onclick='current_sbi=".concat(sbi_autoid, ";'><a href='#sbidetialspg'><img src='images/Folder-Blue-Public-icon.png'/><h3>", sbi_name, "</h3><p>", status, "</p></a><a href='#'>Edit Details</a></li>");
				}
				$('#sbilv').empty();
				$('#sbilv').append(sbilis);
				$('#sbilv').listview("refresh");
			}, null);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function get_current_details() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(tx) {
			var querystring = "SELECT * FROM projecttb where project_id =".concat(current_project);
			tx.executeSql(querystring, [], function(tx, results) {
				var projlis = "<li data-role='list-divider'>Project Details</li>";
				for(var i = 0; i < results.rows.length; i++) {
					var projtaglist = "";
					var project_id = results.rows.item(i)['project_id'];
					var project_name = results.rows.item(i)['project_name'];
					var project_desc = results.rows.item(i)['project_desc'];
					var project_start = results.rows.item(i)['project_startdate'];
					var project_end = results.rows.item(i)['project_enddate'];
					var project_res = results.rows.item(i)['no_of_res'];
					var project_status = results.rows.item(i)['project_status'];
					switch (project_status) {
						case 1:
							project_status = "Not Started";
							break;
						case 2:
							project_status = "In progress";
							break;
						case 3:
							project_status = "Hold";
							break;
						case 4:
							project_status = "Completed";
							break;
					}
					projlis = projlis + "<li>Project ID: ".concat(project_id, '</li>', '<li>Project Name: ', project_name, '</li>', '<li>Project Description: ', project_desc, '</li>', '<li>Project Start Date: ', project_start, '</li>', '<li>Project End Date: ', project_end, '</li>', '<li>No of resources: ', project_res, '</li>', '<li>Project Status: ', project_status, '</li>');
				}
				$('#projectdetailslv').empty();
				$('#projectdetailslv').append(projlis);
				$('#projectdetailslv').listview("refresh");
			}, null);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function get_current_pbi_details() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(tx) {
			var querystring = "SELECT * FROM pbitb WHERE pbi_autoid	=".concat(current_pbi);
			tx.executeSql(querystring, [], function(tx, results) {
				var pbilis = "<li data-role='list-divider'>PBI Details</li>";
				for(var i = 0; i < results.rows.length; i++) {
					var projtaglist = "";
					var pbi_id = results.rows.item(i)['pbi_id'];
					var project_id = results.rows.item(i)['project_id'];
					var priority_id = results.rows.item(i)['priority_id'];
					var pbi_dependency = results.rows.item(i)['pbi_dependency'];
					var sprintid = results.rows.item(i)['sprintid'];
					var estimated_hours = results.rows.item(i)['estimated_hours'];
					var buissness_process = results.rows.item(i)['buissness_process'];
					var grouping = results.rows.item(i)['grouping'];
					var pbi_userstory = results.rows.item(i)['pbi_userstory'];
					var accp_cri = results.rows.item(i)['accp_cri'];
					var status = results.rows.item(i)['status'];
					var initiated_by = results.rows.item(i)['initiated_by'];
					var res = results.rows.item(i)['res'];
					var start_date = results.rows.item(i)['start'];
					var stop_date = results.rows.item(i)['stop'];
					
					 switch (priority_id) {
					 case 1:
					 priority_id = "High";
					 break;
					 case 2:
					 priority_id = "Above Normal";
					 break;
					 case 3:
					 priority_id = "Normal";
					 break;
					 case 4:
					 priority_id = "Below Normal";
					 break;
					 case 5:
					 priority_id = "Lowest";
					 break;
					 }
					 
					 
					 switch (status) {
					 case 1:
					 status = "Not Started";
					 break;
					 case 2:
					 status = "In progress";
					 break;
					 case 3:
					 status = "Hold";
					 break;
					 case 4:
					 status = "Completed";
					 break;
					 }
					 
					pbilis = pbilis + "<li>PBI ID: ".concat(pbi_id, '</li>', '<li>Project ID: ', project_id, '</li>', '<li>Priority: ', priority_id, '</li>', '</li>', '<li>PBI Dependency: ', pbi_dependency, '</li>', '</li>', '</li>', '<li>Sprint ID: ', sprintid, '</li>', '</li>', '<li>Estimated no of Hours: ', estimated_hours, '</li>', '</li>', '<li>Buissness Process: ', buissness_process, '</li>', '</li>', '<li>Grouping: ', grouping, '</li>', '</li>', '<li>PBI UserStory: ', pbi_userstory, '</li>', '</li>', '<li>Acceptance Criteria: ', accp_cri, '</li>', '</li>', '<li>Status: ', status, '</li>', '</li>', '<li>Initiated By: ', initiated_by, '</li>', '</li>', '<li>No of Resources: ', res, '</li>', '</li>', '<li>Start Date: ', start_date, '</li>', '</li>', '<li>Stop Date: ', stop_date, '</li>');
				}
				$('#pbidetailslv').empty();
				$('#pbidetailslv').append(pbilis);
				$('#pbidetailslv').listview("refresh");
			}, null);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function get_current_sbi_details() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(tx) {
			var querystring = "SELECT * FROM sbitb WHERE sbi_autoid	=".concat(current_sbi);
			tx.executeSql(querystring, [], function(tx, results) {
				var sbilis = "<li data-role='list-divider'>SBI Details</li>";
				for(var i = 0; i < results.rows.length; i++) {
					var projtaglist = "";
					var pbi_id = results.rows.item(i)['pbi_id'];
					var sbi_name = results.rows.item(i)['sbi_name'];
					var status = results.rows.item(i)['status'];
					var estimated_hours = results.rows.item(i)['estimated_hours'];
					var completed_hours = results.rows.item(i)['completed_hours'];
					switch (status) {
						case 5:
							status = "In Progress";
							break;
						case 6:
							status = "Review";
							break;
						case 7:
							status = "Blocked";
							break;
						case 8:
							status = "Done";
							break;
					}
					sbilis = sbilis + "<li>PBI ID: ".concat(pbi_id, '</li>', '<li>SBI Name: ', sbi_name, '</li>', '<li>Status: ', status, '</li>', '<li>Estimated hours: ', estimated_hours, '</li>', '<li>Completed Hours: ', completed_hours, '</li>');
				}
				$('#sbidetailslv').empty();
				$('#sbidetailslv').append(sbilis);
				$('#sbidetailslv').listview("refresh");
			}, null);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}


function loadscrumboard_pbi() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.readTransaction(function(tx) {
			var query = "SELECT * FROM pbitb Where project_id=".concat("'", current_project, "'");
			tx.executeSql(query, [], function(tx, results) {
				pbis = results;
				sbis = new Array();
				for(var i = 0; i < pbis.rows.length; i++) {
					var query1 = "SELECT * FROM sbitb Where pbi_id=".concat("'", pbis.rows.item(i)['pbi_autoid'], "'");
					tx.executeSql(query1, [], function(tx1, res) {
						sbis.push(res);
					}, null);
				}
				scrumboardUI();
			}, null);
		});
	} else {
		console.log('initialize the databasefirst');
	}

}

function scrumboardUI() {
	$('#gvlivescrumb').empty();
	$('#gvlivescrumb').append("<div class='ui-block-a canvas_header'>PBI</div><div class='ui-block-b canvas_header'>SBI/Not Started/Blocked</div><div class='ui-block-c canvas_header'>In Progress</div><div class='ui-block-d canvas_header'>Review</div><div class='ui-block-e canvas_header'>Done</div>");

	var auto_id = 0;
	var sbi_no = 1;
	for(var i = 0; i < pbis.rows.length; i++) {
		auto_id = auto_id + 1;
		var pbi_autoid = pbis.rows.item(i)['pbi_autoid'];
		var pbi_id = pbis.rows.item(i)['pbi_id'];
		var pbi_userstory = pbis.rows.item(i)['pbi_userstory'];
		var scrumlis = "";
		scrumlis = scrumlis.concat("<div class='ui-block-a'", "id='canvas_block", auto_id, "'><div class='stickynote_non' id='canvas_st", Math.floor(Math.random() * 100000), "'><span class='pin'></span><h4>PBI ", i + 1, "</h4><p>", pbi_userstory, "</p></div></div>");
		scrumlis = scrumlis.concat("<div class='ui-block-b'", "id='canvas_block", auto_id + 1, "'></div>");
		scrumlis = scrumlis.concat("<div class='ui-block-c'", "id='canvas_block", auto_id + 2, "'></div>");
		scrumlis = scrumlis.concat("<div class='ui-block-d'", "id='canvas_block", auto_id + 3, "'></div>");
		scrumlis = scrumlis.concat("<div class='ui-block-e'", "id='canvas_block", auto_id + 4, "'></div>");

		$('#gvlivescrumb').append(scrumlis);

		for(var j = 0; j < sbis.length; j++) {
			for(var z = 0; z < sbis[j].rows.length; z++) {
				if(sbis[j].rows.item(z)['pbi_id'] == pbi_autoid) {
					console.log('true');
					console.log(sbis[j].rows.item(z)['status']);
					console.log(sbis[j].rows.item(z));
					switch (sbis[j].rows.item(z)['status']) {
						case 5:
							var sbinow = "".concat("<div class='stickynote' id='canvas_st", Math.floor(Math.random() * 100000), "'><span class='pin'></span><h4>SBI ", sbi_no, "</h4><p>", sbis[j].rows.item(z)['sbi_name'], "(", sbis[j].rows.item(z)['completed_hours'], ")", "</p></div>");
							sbi_no = sbi_no + 1;
							var con_id = 'canvas_block'.concat(auto_id + 2);
							var con = document.getElementById(con_id);
							$(con).append(sbinow);

							break;
						case 6:
							var sbinow = "".concat("<div class='stickynote' id='canvas_st", Math.floor(Math.random() * 100000), "'><span class='pin'></span><h4>SBI ", sbi_no, "</h4><p>", sbis[j].rows.item(z)['sbi_name'], "(", sbis[j].rows.item(z)['completed_hours'], ")", "</p></div>");
							sbi_no = sbi_no + 1;
							var con_id = 'canvas_block'.concat(auto_id + 3);
							var con = document.getElementById(con_id);
							$(con).append(sbinow);

							break;
						case 7:
							var sbinow = "".concat("<div class='stickynote' id='canvas_st", Math.floor(Math.random() * 100000), "'><span class='pin'></span><h4>SBI ", sbi_no, "</h4><p>", sbis[j].rows.item(z)['sbi_name'], "(", sbis[j].rows.item(z)['completed_hours'], ")", "</p></div>");
							sbi_no = sbi_no + 1;
							var con_id = 'canvas_block'.concat(auto_id + 1);
							var con = document.getElementById(con_id);
							$(con).append(sbinow);

							break;
						case 8:
							var sbinow = "".concat("<div class='stickynote' id='canvas_st", Math.floor(Math.random() * 100000), "'><span class='pin'></span><h4>SBI ", sbi_no, "</h4><p>", sbis[j].rows.item(z)['sbi_name'], "(", sbis[j].rows.item(z)['completed_hours'], ")", "</p></div>");
							sbi_no = sbi_no + 1;
							var con_id = 'canvas_block'.concat(auto_id + 4);
							var con = document.getElementById(con_id);
							$(con).append(sbinow);
							break;
						default:
							var sbinow = "".concat("<div class='stickynote' id='canvas_st", Math.floor(Math.random() * 100000), "'><span class='pin'></span><h4>SBI ", sbi_no, "</h4><p>", sbis[j].rows.item(z)['sbi_name'], "(", sbis[j].rows.item(z)['completed_hours'], ")", "</p></div>");
							sbi_no = sbi_no + 1;
							var con_id = 'canvas_block'.concat(auto_id + 1);
							var con = document.getElementById(con_id);
							$(con).append(sbinow);

							break;
					}
				}
			};

			//console.log(sbis[j]);
		};
		auto_id = auto_id + 4;
	}
	$(".stickynote").mobiledraganddrop({
		targets : ".ui-block-b,.ui-block-c,.ui-block-d,.ui-block-e"
	});
}


//Update SBI number of hours
function sbiupdatecompleted_hours() {
	
	var sbi_hrs=$('#sbi_details_update_hrs').val();
	
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(transaction) {
			var query = "UPDATE sbitb set completed_hours='".concat(sbi_hrs ,"' WHERE sbi_autoid='",current_sbi,"';");
			transaction.executeSql(query);
		});
	} else {
		console.log('initialize the databasefirst');
	}
	get_current_sbi_details();
	$('#sbi_details_update_hrs').val("");
}

function delete_cuurent_sbi() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(transaction) {
			var query = "DELETE FROM sbitb WHERE sbi_autoid='".concat(current_sbi,"';");
			transaction.executeSql(query);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function delete_current_pbi() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(transaction) {
			var query = "DELETE FROM sbitb WHERE pbi_id='".concat(current_pbi,"';");
			transaction.executeSql(query);
			
			var query1 = "DELETE FROM pbitb WHERE pbi_autoid='".concat(current_pbi,"';");
			transaction.executeSql(query1);
			
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function delete_pbi(pbi_id) {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(transaction) {
			var query = "DELETE FROM sbitb WHERE pbi_id='".concat(pbi_id,"';");
			transaction.executeSql(query);
			
			var query1 = "DELETE FROM pbitb WHERE pbi_autoid='".concat(pbi_id,"';");
			transaction.executeSql(query1);
			
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function delete_current_project() {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(tx) {
			var query = "SELECT * FROM pbitb Where project_id=".concat("'", current_project, "'");
			tx.executeSql(query, [], function(tx, results) {
				for(var i = 0; i < results.rows.length; i++) {
					var pbi_autoid = results.rows.item(i)['pbi_autoid'];
					delete_pbi(pbi_autoid);
				}
				delete_proj(current_project);
			}, null);
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

function delete_proj(proj_id) {
	var db = window.openDatabase("ScrumDB", "", "My Scrum database", 1024);
	if(db.version == "1") {
		db.transaction(function(transaction) {
			var query = "DELETE FROM projecttb WHERE project_id='".concat(proj_id,"';");
			transaction.executeSql(query);
			
		});
	} else {
		console.log('initialize the databasefirst');
	}
}

(function($) {
	initDatabase();

	//projects page
	$('#projectspg').live('pageshow', function(event, ui) {
		get_projects_list();
	});
	//project details page
	$('#projdetialspg').live('pageshow', function(event, ui) {
		get_current_details();
	});
	//pbi details page
	$('#pbidetialspg').live('pageshow', function(event, ui) {
		get_current_pbi_details();
	});
	//sbi details page
	$('#sbidetialspg').live('pageshow', function(event, ui) {
		get_current_sbi_details();
	});
	//PBI list page
	$('#pbilistpg').live('pageshow', function(event, ui) {
		get_pbi_list();
	});
	//SBI List page
	$('#sbilistpg').live('pageshow', function(event, ui) {
		get_sbi_list();
	});
	/*Add Pages*/

	//SBI Add page
	$('#sbiaddpg').live('pageshow', function(event, ui) {
		$('#addsbi_name').val("");
		$('#addsbi_esthours').val("");
		$('#addsbi_completedhrs').val("0");
		$('#addsbi_completedhrs').slider('refresh');
	});
	//Project Add Page
	$('#addprojpg').live('pageshow', function(event, ui) {
		$('#addproj_projid').val("");
		$('#addproj_name').val("");
		$('#addproj_desc').val("");
		//addproj_status
		$('#addproj_status').selectmenu('refresh');
		$('#addproj_startdate').val("");
		$('#addproj_enddate').val("");
		$('#addproj_enddate').val("");
		$('#addproj_res').val("");
	});
	
	//pbi add page
	$('#pbiaddpg').live('pageshow', function(event, ui) {
	$('#addpbi_id').val("");
	//var pbi_priid = $('#addpbi_proid :selected').text();
	$('#addpbi_dependency').val("");
	$('#add_pbi_buiss').val("");
	$('#addpbi_grouping').val("");
	$('#addpbi_userstory').val("");
	$('#addpbi_accpcri').val("");
	
	
	//var pbi_status = $('#addpbi_status :selected').text();
	
	$('#addpbi_res').val("");
	$('#addpbi_init').val("");
	$('#addpbi_startdate').val("");
	$('#addpbi_end').val("");
	
	});
	
	
	//ScrumBoard
	$('#liveboardpg').live('pageshow', function(event, ui) {
		loadscrumboard_pbi();
	});
	
})(jQuery);
